package org.dbutils.dao.impl;

import org.dbutils.dao.StaffDao;
import org.dbutils.entity.Staff;
import org.dbutils.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * StaffDao接口实现-基于原生JDBC实现
 *
 * @author CSY
 * @date 2025 - 02 - 25
 */
public class StaffDaoImpl4JDBC implements StaffDao {
    @Override
    public int insert(Staff staff) {
        int row = 0;
        String sql = "insert into staffs(name,age,phone,sta_pos) values (?,?,?,?);";
        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, staff.getName());
            psmt.setInt(2, staff.getAge());
            psmt.setString(3, staff.getPhone());
            psmt.setString(4, staff.getStaPos());
            row = psmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn,psmt,null);
        }
        return row;
    }

    @Override
    public int update(Staff staff) {
        int row = 0;
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=? where id = ?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setString(1, staff.getName());
            psmt.setInt(2, staff.getAge());
            psmt.setString(3, staff.getPhone());
            psmt.setString(4, staff.getStaPos());
            psmt.setInt(5, staff.getId());
            row = psmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn,psmt,null);
        }
        return row;
    }

    @Override
    public int delete(int id) {
        int row = 0;
        String sql = "delete from staffs where id = ?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);
            row = psmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn,psmt,null);
        }
        return row;
    }

    @Override
    public List<Staff> selectByPage(int currentPage, int pageSize) {
        List<Staff> list = new ArrayList<Staff>();
        String sql = "select * from staffs limit ?,?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, (currentPage - 1) * pageSize);
            psmt.setInt(2, pageSize);
            rs = psmt.executeQuery();
            while (rs.next()) {
                Staff staff = new Staff();
                staff.setId(rs.getInt("id"));
                staff.setName(rs.getString("name"));
                staff.setAge(rs.getInt("age"));
                staff.setPhone(rs.getString("phone"));
                staff.setStaPos(rs.getString("sta_pos"));
                staff.setAddTime(rs.getTimestamp("add_time").toLocalDateTime());
                staff.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());
                list.add(staff);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn,psmt,rs);
        }
        return list;
    }

    @Override
    public Staff selectById(int id) {
        Staff staff = null;
        String sql = "select * from staffs where id=?";
        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1, id);
            rs = psmt.executeQuery();
            while (rs.next()) {
                staff = new Staff();
                staff.setId(rs.getInt("id"));
                staff.setName(rs.getString("name"));
                staff.setAge(rs.getInt("age"));
                staff.setPhone(rs.getString("phone"));
                staff.setStaPos(rs.getString("sta_pos"));
                staff.setAddTime(rs.getTimestamp("add_time").toLocalDateTime());
                staff.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(conn,psmt,rs);
        }
        return staff;
    }
}
